﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DAL
{
    public partial class ImportService
    {
        CRMEntities db;

        public void ImportCustomer(DataTable table, int sourceid, int userid)
        {
            db = new CRMEntities();

            if (table != null)
                try
                {
                    List<Customer> listnewcus = new List<Customer>();
                    //get a list of existed and new customer is true
                    foreach (DataRow row in table.Rows)
                    {
                        string cellphonenumber = row["CellPhoneNumber"].ToString().Trim();
                        string homephonenumber = row["HomePhoneNumber"].ToString().Trim();
                        string officephonenumber = row["OfficePhoneNumber"].ToString().Trim();

                        // check for row cellphone
                        var newcuscellphonenumber = (from c in db.Customers
                                                     where c.CellPhoneNumber == cellphonenumber &&
                                                    cellphonenumber != ""                                                    
                                                    select c).SingleOrDefault();
                        if (newcuscellphonenumber != null && newcuscellphonenumber.EDD.HasValue)
                        {
                            if ((280 + (ServerDateTime.SystemDateTime - (DateTime)newcuscellphonenumber.EDD).Days) >= 300 &&
                                newcuscellphonenumber.EDD < ServerDateTime.SystemDateTime)
                            {
                                listnewcus.Add(newcuscellphonenumber);
                            }
                        }
                        else if (newcuscellphonenumber != null && !newcuscellphonenumber.EDD.HasValue)
                        {
                            listnewcus.Add(newcuscellphonenumber);
                        }

                        //check for row homephone
                        var newcushomephonenumber = (from c in db.Customers
                                                     where c.CellPhoneNumber == homephonenumber &&
                                                            homephonenumber != ""
                                                     select c).SingleOrDefault();
                        if (newcushomephonenumber != null && newcushomephonenumber.EDD.HasValue)
                        {
                            if ( (280 + (ServerDateTime.SystemDateTime - (DateTime)newcushomephonenumber.EDD).Days) >= 300 &&
                                newcushomephonenumber.EDD < ServerDateTime.SystemDateTime)
                            {
                                listnewcus.Add(newcushomephonenumber);
                            }
                        }
                        else if (newcushomephonenumber != null && !newcushomephonenumber.EDD.HasValue)
                        {
                            listnewcus.Add(newcushomephonenumber);
                        }

                        //check for row officephone
                        var newcusofficephonenumber = (from c in db.Customers
                                                       where c.CellPhoneNumber == officephonenumber &&
                                                            officephonenumber != ""
                                                     select c).SingleOrDefault();
                        if (newcusofficephonenumber != null && newcusofficephonenumber.EDD.HasValue)
                        {
                            if (
                                (280 + (ServerDateTime.SystemDateTime - (DateTime)newcusofficephonenumber.EDD).Days) >= 300 &&
                                newcusofficephonenumber.EDD < ServerDateTime.SystemDateTime)
                            {
                                listnewcus.Add(newcusofficephonenumber);
                            }
                        }
                        else if (newcusofficephonenumber != null && !newcusofficephonenumber.EDD.HasValue)
                        {
                            listnewcus.Add(newcusofficephonenumber);
                        }
                    }
                    //distinct list
                    listnewcus.AsEnumerable().Distinct().ToList();
                    DateTime dttmp;
                    // Import each rows to each record in database
                    foreach (DataRow row in table.Rows)
                    {                        
                        Customer cus = new Customer();
                        string currentbrand = row["CurrentBrandName"].ToString().Trim();
                        string previousbrand = row["PreviousBrandName"].ToString().Trim();
                        string cellphonenumber = row["CellPhoneNumber"].ToString().Trim();
                        string homephonenumber = row["HomePhoneNumber"].ToString().Trim();
                        string officephonenumber = row["OfficePhoneNumber"].ToString().Trim();
                        string EDDS = row["EDDS"].ToString().Trim();
                        var existedcus = listnewcus.Where(a => a.CellPhoneNumber == cellphonenumber && a.CellPhoneNumber != "" ||
                                                        a.HomePhoneNumber == homephonenumber && a.HomePhoneNumber != "" ||
                                                        a.OfficePhoneNumber == officephonenumber && a.OfficePhoneNumber != "").SingleOrDefault();
                        //If existed and new customer is true
                        if (existedcus != null)
                        {
                            existedcus.Source_ID = sourceid;
                            existedcus.LastUpdatedBy = userid;
                            existedcus.Collector_ID = int.Parse(row["Collector_ID"].ToString().Trim());
                            existedcus.FirstName = row["FirstName"].ToString().Trim();
                            existedcus.ClinicInfo = row["ClinicInfo"].ToString().Trim();
                            existedcus.LastName = row["LastName"].ToString().Trim();
                            if (!string.IsNullOrWhiteSpace(currentbrand))
                            {
                                existedcus.CurrentBrandName = int.Parse(currentbrand);
                            }
                            if (!string.IsNullOrWhiteSpace(previousbrand))
                            {
                                existedcus.PreviousBrandName = int.Parse(previousbrand);
                            }
                            if (DateTime.TryParse(row["CollectedDate"].ToString(), out dttmp)) existedcus.CollectedDate = dttmp;
                            else
                            {
                                existedcus.CollectedDate = null;
                            }

                            if (string.IsNullOrEmpty(row["HomePhoneNumber"].ToString())) existedcus.HomePhoneNumber = null;
                            else
                            {
                                existedcus.HomePhoneNumber = row["HomePhoneNumber"].ToString().Trim();
                            }
                            if (string.IsNullOrEmpty(row["OfficePhoneNumber"].ToString())) existedcus.OfficePhoneNumber = null;
                            else
                            {
                                existedcus.OfficePhoneNumber = row["OfficePhoneNumber"].ToString().Trim();
                            }
                            if (string.IsNullOrEmpty(row["CellPhoneNumber"].ToString())) existedcus.CellPhoneNumber = null;
                            else
                            {
                                existedcus.CellPhoneNumber = row["CellPhoneNumber"].ToString().Trim();
                            }
                            existedcus.Street = row["Street"].ToString().Trim();
                            existedcus.Substreet = row["Substreet"].ToString().Trim();

                            if (string.IsNullOrEmpty(row["Ward_Code"].ToString())) existedcus.Ward_Code = null;
                            else
                            {
                                existedcus.Ward_Code = int.Parse(row["Ward_Code"].ToString());
                            }
                            if (string.IsNullOrEmpty(row["District_Code"].ToString())) existedcus.District_Code = null;
                            else
                            {
                                existedcus.District_Code = int.Parse(row["District_Code"].ToString());
                            }
                            if (string.IsNullOrEmpty(row["Province_Code"].ToString())) existedcus.Province_Code = null;
                            else
                            {
                                existedcus.Province_Code = int.Parse(row["Province_Code"].ToString());
                            }
                            if (DateTime.TryParse(row["EDD"].ToString(), out dttmp)) existedcus.EDD = dttmp;
                            else
                            {
                                existedcus.EDD = null;
                            }

                                //KPI
                            if (int.Parse(currentbrand) == 14 && int.Parse(previousbrand) == 14)
                            {
                                existedcus.CustomerStatus = 5;
                                existedcus.CallStatus = 7;
                            }

                                //E1
                            else if (int.Parse(currentbrand) == 14 && int.Parse(previousbrand) != 14 && (EDDS == "BORN" || EDDS == "TR3"))     // Nhan E1
                            {
                                existedcus.CustomerStatus = 3;
                                existedcus.CallStatus = 5;

                            }else if (int.Parse(currentbrand) == 14 && int.Parse(previousbrand) != 14 && (EDDS == "BLANK" || EDDS == "TR1" || EDDS == "TR2"))       //Loai E1 
                            {
                                existedcus.CustomerStatus = 4;
                                existedcus.CallStatus = null;
                            }

                                //C1
                            else if (int.Parse(currentbrand) != 14 && (EDDS == "TR1" || EDDS == "TR2" || EDDS == "BLANK"))        //Nhan C1
                            {
                                existedcus.CustomerStatus = 3;
                                existedcus.CallStatus = 2;
                            }
                            else if (int.Parse(currentbrand) != 14 && (EDDS == "TR3" || EDDS == "BORN"))      //Loai C1
                            {
                                existedcus.CustomerStatus = 4;
                                existedcus.CallStatus = null;
                            }
                            
                            db.Entry(existedcus).State = EntityState.Modified;
                        }
                        //If not existed and new customer is true
                        else
                        {
                            cus.Source_ID = sourceid;
                            cus.LastUpdatedBy = userid;
                            cus.Collector_ID = int.Parse(row["Collector_ID"].ToString().Trim());
                            cus.FirstName = row["FirstName"].ToString().Trim();
                            cus.ClinicInfo = row["ClinicInfo"].ToString().Trim();
                            cus.LastName = row["LastName"].ToString().Trim();
                            if (!string.IsNullOrWhiteSpace(currentbrand))
                            {
                                cus.CurrentBrandName = int.Parse(currentbrand);
                            }
                            if (!string.IsNullOrWhiteSpace(previousbrand))
                            {
                                cus.PreviousBrandName = int.Parse(previousbrand);
                            }
                            if (DateTime.TryParse(row["CollectedDate"].ToString().Trim(), out dttmp)) cus.CollectedDate = dttmp;
                            else
                            {
                                cus.CollectedDate = null;
                            }

                            if (string.IsNullOrEmpty(row["HomePhoneNumber"].ToString())) cus.HomePhoneNumber = null;
                            else
                            {
                                cus.HomePhoneNumber = row["HomePhoneNumber"].ToString().Trim();
                            }
                            if (string.IsNullOrEmpty(row["OfficePhoneNumber"].ToString())) cus.OfficePhoneNumber = null;
                            else
                            {
                                cus.OfficePhoneNumber = row["OfficePhoneNumber"].ToString().Trim();
                            }
                            if (string.IsNullOrEmpty(row["CellPhoneNumber"].ToString())) cus.CellPhoneNumber = null;
                            else
                            {
                                cus.CellPhoneNumber = row["CellPhoneNumber"].ToString().Trim();
                            }
                            cus.Street = row["Street"].ToString().Trim();
                            cus.Substreet = row["Substreet"].ToString().Trim();

                            if (string.IsNullOrEmpty(row["Ward_Code"].ToString())) cus.Ward_Code = null;
                            else
                            {
                                cus.Ward_Code = int.Parse(row["Ward_Code"].ToString());
                            }
                            if (string.IsNullOrEmpty(row["District_Code"].ToString())) cus.District_Code = null;
                            else
                            {
                                cus.District_Code = int.Parse(row["District_Code"].ToString());
                            }
                            if (string.IsNullOrEmpty(row["Province_Code"].ToString())) cus.Province_Code = null;
                            else
                            {
                                cus.Province_Code = int.Parse(row["Province_Code"].ToString());
                            }
                            if (DateTime.TryParse(row["EDD"].ToString(), out dttmp)) cus.EDD = dttmp;
                            else
                            {
                                cus.EDD = null;
                            }

                                //KPI
                            if (int.Parse(currentbrand) == 14 && int.Parse(previousbrand) == 14)
                            {
                                cus.CustomerStatus = 5;
                                cus.CallStatus = 7;
                            }
                                //E1
                            else if (int.Parse(currentbrand) == 14 && int.Parse(previousbrand) != 14 && (EDDS == "BORN" || EDDS == "TR3"))     // Nhan E1
                            {
                                cus.CustomerStatus = 3;
                                cus.CallStatus = 5;

                            }
                            else if (int.Parse(currentbrand) == 14 && int.Parse(previousbrand) != 14 && (EDDS == "BLANK" || EDDS == "TR1" || EDDS == "TR2"))       //Loai E1 
                            {
                                cus.CustomerStatus = 4;
                                cus.CallStatus = null;
                            }

                               //C1
                            else if (int.Parse(currentbrand) != 14 && (EDDS == "TR1" || EDDS == "TR2" || EDDS == "BLANK"))        //Nhan C1
                            {
                                cus.CustomerStatus = 3;
                                cus.CallStatus = 2;
                            }
                            else if (int.Parse(currentbrand) != 14 && (EDDS == "TR3" || EDDS == "BORN"))      //Loai C1
                            {
                                cus.CustomerStatus = 4;
                                cus.CallStatus = null;
                            }
                            // Add to Customers Entity
                            db.Customers.Add(cus);
                        }
                    }

                    // Save Change and insert record to database
                    db.SaveChanges();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    //Free resource
                    table.Dispose();
                }
            else
            {
                return;
            }
        }

        public void ImportNewCustomer(DataTable table, SourceEntity source)
        {
            db = new CRMEntities();
            UserInfoEntity user = new UserInfoEntity();
            //Import Source
                Source src = new Source()
                {
                    SourceName = source.SourceName,
                    ImportDate = source.ImportDate,
                    TotalData = (int)source.TotalData,
                    TotalWrongData = source.TotalWrongData,
                    TotalDup = source.TotalDup,
                    TotalDeDup = source.TotalDeDup
                };
            try
            {                
                db.Sources.Add(src);
                db.SaveChanges();
                //Import to Customer
                ImportCustomer(table, src.Source_ID, user.UserID);
            }
            catch
            {
                db.Sources.Remove(src);
                db.SaveChanges();
            }
        }

        public bool ImportDWE(List<DividingWorkingEntity> listDWE)
        {
            db = new CRMEntities();
            try
            {
                foreach (var item in listDWE)
                {
                    DividingWorking dw = new DividingWorking();
                    dw.User_ID = item.User_ID;
                    dw.Customer_ID = item.Customer_ID;
                    dw.CallStatus_ID = (int)item.CallStatus_ID;

                    db.DividingWorkings.Add(dw);
                }

                db.SaveChanges();
                return true;
            }
            catch
            {
                return false;

            }
            finally
            {
                // Clear data in Customers Entity
                db = null;
            }
        }

    }
}
